using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;
using Deloitte.MSExcel;
using Deloitte.Windows.Forms;
namespace Deloitte.MSExcel
{
    public delegate void ExcelRowExportedEventHandler(ExcelRowExportedEventArgs e);
    class DataTableExcelExport : IExportToExcel, IDisposable
    {
        public event ExcelRowExportedEventHandler ExcelRowExportedEvent;
        public System.ComponentModel.BackgroundWorker patientFormBackgroundWorker;
        public System.Timers.Timer timer;			
        private PatientForm patientForm;
        private Excel.Application xlApplication;
        private Excel.Workbook xlWorkbook;
        private Excel.Worksheet xlWorksheet;
        private bool isPatientFormDisplayed;
        private string xlWorkbookPath;
        private int exportedRows;        
        private Excel.Range startingRange;
        private int rowOffset;
        private int columnOffset;
        private MSExcel.NumberFormatMappingDictionary numberFormatMapping;
        private bool isAutoFilter;
        private DataTable exportTable;
        public bool isColumnHeadersExported;
        public DataTableExcelExport(DataTable table)
        {
            this.ExportTable = table;
            this.CommonConstructorCode();
        }

        public bool IsPatientFormDisplayed
        {
            get { return this.isPatientFormDisplayed; }
            set { this.isPatientFormDisplayed = value; }
        }
        /// <summary>
        /// Gets and sets the file path to the Excel Workbook
        /// </summary>
        public string WorkbookPath
        {
            get
            {
                if (this.xlWorkbookPath == null){this.WorkbookPath = this.xlWorkbook.FullName;}                
                return this.xlWorkbookPath; 
            }
            set { this.xlWorkbookPath = value; }
        }
        public DataTableExcelExport()
        {
            this.CommonConstructorCode();
        }
        public int TotalRows
        {
            get { return this.exportTable.Rows.Count; }
        }
        public int ExportedRows
        {
            get { return this.exportedRows; }
        }
        private void CommonConstructorCode()
        {
            this.xlApplication = new Excel.Application();
            this.numberFormatMapping = new NumberFormatMappingDictionary();
            foreach (DataColumn dc in this.exportTable.Columns)
            {
                this.numberFormatMapping.Add(dc.ColumnName, Deloitte.MSExcel.ExcelHelper.ConvertDotNetTypeToNumberFormat(dc.DataType));
            }
            
            this.timer = new System.Timers.Timer();
            this.timer.Enabled = false;
            this.timer.Elapsed += new System.Timers.ElapsedEventHandler(timer_Elapsed);
            this.exportedRows = 0;
            this.rowOffset = 1;
            this.columnOffset = 1;
            this.IsColumnHeadersExported = true;
        }

        void timer_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
        {
            
        }
        public bool IsColumnHeadersExported
        {
            get { return this.isColumnHeadersExported; }
            set { this.isColumnHeadersExported = value; }
        }
        public DataTable ExportTable
        {
            get { return this.exportTable; }
            set { this.exportTable = value; }
        }
        public PatientForm PatientForm
        {
            get { return this.patientForm; }
            set { this.patientForm = value; }
        }

        #region IExportToExcel Members

        void IExportToExcel.ExportToExcel()
        {           
			try
			{               
                if (this.isPatientFormDisplayed)
                {
                    if (this.patientForm == null) {
                        SetPatientFormProperties();
                    }
                    if (this.patientFormBackgroundWorker == null)
                    {
                        CreatePatientFormBackgroundWorker();
                        this.patientFormBackgroundWorker.RunWorkerAsync();
                        this.patientForm.ShowDialog();
                    }
                }
                else { DoExport(); }
			}						
			catch(Exception ex)
			{
				throw new Exception(ex.ToString());
			}
			finally
			{
				if(this.xlApplication!=null)
				{
                    xlApplication.ScreenUpdating = true;
                    xlApplication.Visible = true;                    
				}				
			}		
        }

        private void SetPatientFormProperties()
        {
            this.patientForm = new PatientForm();
            this.patientForm.Timer.Tick += new EventHandler(Timer_Tick);
            this.patientForm.Timer.Enabled = true;
            this.patientForm.ProgressBar.Minimum = 0;
            this.patientForm.ProgressBar.Maximum = this.TotalRows;                     
        }

        void Timer_Tick(object sender, EventArgs e)
        {
            this.patientForm.BringToFront();
            this.patientForm.ProgressBar.Value = this.exportedRows;
        }

        private void DoExport()
        {
            this.exportedRows = 0;
            this.xlApplication.Visible = true;
            this.xlApplication.ScreenUpdating = false;
            int r = 0, c = 0;
            object missing = Type.Missing;
            this.xlWorksheet.Select(missing);
            ExportColumnHeaders(ref r, ref c);
            SetNumberFormats(r, ref c);
            c = 0;
            ExportRows(ref r, ref c);
            //add the auto filter
            AddAutoFilter();
        }

        private void CreatePatientFormBackgroundWorker()
        {
            this.patientFormBackgroundWorker = new System.ComponentModel.BackgroundWorker();
            this.patientFormBackgroundWorker.WorkerReportsProgress = true;
            this.patientFormBackgroundWorker.DoWork += new System.ComponentModel.DoWorkEventHandler(patientFormBackgroundWorker_DoWork);
            this.patientFormBackgroundWorker.ProgressChanged += new System.ComponentModel.ProgressChangedEventHandler(patientFormBackgroundWorker_ProgressChanged);
            this.patientFormBackgroundWorker.RunWorkerCompleted += new System.ComponentModel.RunWorkerCompletedEventHandler(patientFormBackgroundWorker_RunWorkerCompleted);
        }

        void patientFormBackgroundWorker_RunWorkerCompleted(object sender, System.ComponentModel.RunWorkerCompletedEventArgs e)
        {
            if (this.patientForm != null)
            {
                this.patientForm.Timer.Enabled = false;
                this.patientForm.Hide();
            }
        }

        void patientFormBackgroundWorker_ProgressChanged(object sender, System.ComponentModel.ProgressChangedEventArgs e)
        {
            //todo: implement some logic here
        }

        void patientFormBackgroundWorker_DoWork(object sender, System.ComponentModel.DoWorkEventArgs e)
        {
            try
            {                
                this.DoExport();
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }           
        }

        private void ExportRows(ref int r, ref int c)
        {
            //add the data from the each row to the excel sheet
            foreach (DataRow myDataRow in this.ExportTable.Rows)
            {                	
                foreach (DataColumn myDataColumn in this.ExportTable.Columns)
                {
                    startingRange.get_Offset(r, c).Value2 = myDataRow[myDataColumn].ToString();
                    //skip the desired number of columns (column offset)
                    c += this.columnOffset;
                }
                c = 0;
                //skip the desired rows (row offset)
                r += rowOffset;
                //if (this.backgroundExport != null) { this.backgroundExport.ReportProgress((int)((this.exportedRows/this.TotalRows)*10.0)); }
                this.DispatchExcelRowExportedEvent(myDataRow);
            }
        }
        private void DispatchExcelRowExportedEvent(DataRow row)
        {
            this.exportedRows += 1;
            ExcelRowExportedEvent(new ExcelRowExportedEventArgs(row));
        }

        private void AddAutoFilter()
        {
            if (this.isAutoFilter)
            {
                this.xlWorksheet.get_Range(startingRange.get_Address(false, false, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, Missing.Value, Missing.Value).ToString() + ":" + startingRange.get_Offset(0, this.ExportTable.Columns.Count * columnOffset - 1).get_Address(false, false, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, Missing.Value, Missing.Value).ToString(), Missing.Value).Select();
                Microsoft.VisualBasic.CompilerServices.LateBinding.LateCall(this.xlApplication.Selection, null, "AutoFilter", new object[0], null, null);
            }
        }

        private void SetNumberFormats(int r, ref int c)
        {            
            int i = 0;
            Excel.Range selectedRange;
            if (this.numberFormatMapping != null)
            {
                for (c = 0; c < this.exportTable.Columns.Count; c++)
                {
                    try
                    {
                        selectedRange = (Excel.Range)startingRange.get_Offset(r, i).EntireColumn;
                       if(this.numberFormatMapping.ContainsKey(this.exportTable.Columns[c].ColumnName))
                       {
                           selectedRange.NumberFormat = this.numberFormatMapping[this.exportTable.Columns[c].ColumnName];
                       }
                        else{selectedRange.NumberFormat = "General";}
                    }
                    catch {  }
                    i += (this.columnOffset);
                }
            }
            else
            {
                for (c = 0; c < this.ExportTable.Columns.Count; c++)
                {
                    selectedRange = (Excel.Range)startingRange.get_Offset(r, c).EntireColumn;
                    try{selectedRange.NumberFormat = "General";}
                    catch{}                    
                }
            }
        }

        private void ExportColumnHeaders(ref int r, ref int c)
        {
            if (this.isColumnHeadersExported)
            {
                foreach (DataColumn myDataColumn in this.exportTable.Columns)
                {
                    this.startingRange.get_Offset(r, c).Value2 = myDataColumn.ColumnName;
                    this.startingRange.get_Offset(r, c).Font.Bold = true;
                    c += columnOffset;
                }
                c = 0;
                r++;
            }
        }

        Microsoft.Office.Interop.Excel.Application IExportToExcel.XLApplication
        {

            get { return this.xlApplication; }
            set { this.xlApplication = value; }
        }
        Microsoft.Office.Interop.Excel.Workbook IExportToExcel.XLWorkbook
        {
            get
            {
                return this.xlWorkbook;
            }
            set
            {
                this.xlWorkbook = value;
            }
        }

        Microsoft.Office.Interop.Excel.Worksheet IExportToExcel.XLWorksheet
        {
            get
            {
                return this.xlWorksheet;
            }
            set
            {
                this.xlWorksheet = value;
            }
        }

        NumberFormatMappingDictionary IExportToExcel.NumberFormatMapping
        {
            get
            {
                return this.numberFormatMapping;
            }
            set
            {
                this.numberFormatMapping = value;
            }
        }

        bool IExportToExcel.IsAutoFilter
        {
            get
            {
                return this.isAutoFilter;
            }
            set
            {
                this.isAutoFilter = value;
            }
        }

        Microsoft.Office.Interop.Excel.Range IExportToExcel.StartingRange
        {
            get
            {
                return this.startingRange;
            }
            set
            {
                this.startingRange = value;
            }
        }

        int IExportToExcel.RowOffset
        {
            get
            {
                return this.rowOffset;
            }
            set
            {
                this.rowOffset = value;
            }
        }

        int IExportToExcel.ColumnOffset
        {
            get
            {
                return this.columnOffset;
            }
            set
            {
                this.columnOffset = value;
            }
        }

        #endregion

        #region IDisposable Members

        void IDisposable.Dispose()
        {
            if (this.xlApplication != null)
            {
                xlApplication.Quit();
                xlApplication = null;
                GC.Collect();
                GC.WaitForPendingFinalizers();
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
        }

        #endregion
    }
}